package cn.tangjiabao.halodb.spring.template.impl;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.util.Assert;

import cn.tangjiabao.halodb.dbutils.ProcRunner;
import cn.tangjiabao.halodb.spring.template.JdbcTemplate;

public class JdbcTemplateImpl implements JdbcTemplate{
	/**
	 * This class is thread safe
	 */
	private QueryRunner queryRunner = new QueryRunner(true);
	private ProcRunner procRunner = new ProcRunner(true);
	private DataSource dataSource;
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	public JdbcTemplateImpl() {
    }
    public JdbcTemplateImpl(DataSource dataSource) {
    	setDataSource(dataSource);
    }

	public QueryRunner getQueryRunner() {
		return this.queryRunner;
	}
	public ProcRunner getProcRunner() {
		return this.procRunner;
	}

	public DataSource getDataSource() {
		return this.dataSource;
	}

	/**
	 * Execute an SQL SELECT query without any replacement parameters.
	 * 
	 * @param <T> The type of object that the handler returns
	 * @param sql The query to execute.
	 * @param rsh The handler that converts the results into an object.
	 * @return The object returned by the handler.
	 * @throws DataAccessException if a database access error occurs
	 */
	public <T> T query(final String sql, final ResultSetHandler<T> rsh)
			throws DataAccessException {
		class ExecuteCallback implements DbUtilsCallback<T> {
			@Override
			public T execute(Connection connection) throws SQLException {
				return getQueryRunner().query(connection, sql, rsh);
			}
		}
		return execute(new ExecuteCallback());
	}

	/**
	 * Execute an SQL SELECT query with replacement parameters.
	 * 
	 * @param <T> The type of object that the handler returns
	 * @param sql The query to execute.
	 * @param rsh The handler that converts the results into an object.
	 * @param params The replacement parameters.
	 * @return The object returned by the handler.
	 * @throws DataAccessException if a database access error occurs
	 */
	public <T> T query(final String sql, final ResultSetHandler<T> rsh,
			final Object... params) throws DataAccessException {
		class ExecuteCallback implements DbUtilsCallback<T> {
			@Override
			public T execute(Connection connection) throws SQLException {
				return getQueryRunner().query(connection, sql, rsh, params);
			}
		}
		return execute(new ExecuteCallback());
	}
	public <T> T queryProc(final String sql, final ResultSetHandler<T> rsh,
			final Object... params) throws DataAccessException {
		class ExecuteCallback implements DbUtilsCallback<T> {
			@Override
			public T execute(Connection connection) throws SQLException {
				return getProcRunner().queryProc(connection, sql, rsh, params);
			}
		}
		return execute(new ExecuteCallback());
	}
	

	/**
	 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries with replacement parameters.
	 *
	 * @param sql The SQL to execute.
	 * @param params An array of query replacement parameters.  Each row in
	 * this array is one set of batch replacement values.
	 * @return The number of rows updated per statement.
	 * @throws DataAccessException if a database access error occurs
	 */
	public int[] batch(final String sql, final Object[][] params)
			throws DataAccessException {
		class ExecuteCallback implements DbUtilsCallback<int[]> {
			@Override
			public int[] execute(Connection connection) throws SQLException {
				return getQueryRunner().batch(connection, sql, params);
			}
		}
		return execute(new ExecuteCallback());
	}

	/**
	 * Execute an SQL INSERT query without any replacement parameters.
	 * 
	 * @param <T> The type of object that the handler returns
	 * @param sql The SQL to execute.
	 * @param rsh The handler used to create the result object from
	 * the <code>ResultSet</code> of auto-generated keys.
	 * @return An object generated by the handler.
	 * @throws DataAccessException if a database access error occurs
	 */
	public <T> T insert(final String sql, final ResultSetHandler<T> rsh)
			throws DataAccessException {
		class ExecuteCallback implements DbUtilsCallback<T> {
			@Override
			public T execute(Connection connection) throws SQLException {
				return getQueryRunner().insert(connection, sql, rsh,
						(Object[]) null);
			}
		}
		return execute(new ExecuteCallback());
	}

	/**
	 * Execute an SQL INSERT query with replacement parameters.
	 * 
	 * @param <T> The type of object that the handler returns
	 * @param sql The SQL to execute.
	 * @param rsh The handler used to create the result object from
	 * the <code>ResultSet</code> of auto-generated keys.
	 * @return An object generated by the handler.
	 * @throws DataAccessException if a database access error occurs
	 */
	public <T> T insert(final String sql, final ResultSetHandler<T> rsh,
			final Object... params) throws DataAccessException {
		class ExecuteCallback implements DbUtilsCallback<T> {
			@Override
			public T execute(Connection connection) throws SQLException {
				return getQueryRunner().insert(connection, sql, rsh, params);
			}
		}
		return execute(new ExecuteCallback());
	}

	/**
	 * Executes the given batch of INSERT SQL statements with replacement parameters.
	 * 
	 * @param <T> The type of object that the handler returns
	 * @param sql The SQL to execute.
	 * @param rsh The handler used to create the result object from
	 * the <code>ResultSet</code> of auto-generated keys.
	 * @param params The query replacement parameters.
	 * @return The result generated by the handler.
	 * @throws DataAccessException if a database access error occurs
	 */
	public <T> T insertBatch(final String sql, final ResultSetHandler<T> rsh,
			final Object[][] params) throws DataAccessException {
		class ExecuteCallback implements DbUtilsCallback<T> {
			@Override
			public T execute(Connection connection) throws SQLException {
				return getQueryRunner().insertBatch(connection, sql, rsh, params);
			}
		}
		return execute(new ExecuteCallback());
	}

	/**
	 * Execute an SQL INSERT, UPDATE, or DELETE query without any replacement parameters.
	 *
	 * @param sql The SQL to execute.
	 * @return The number of rows updated.
	 * @throws DataAccessException if a database access error occurs
	 */

	public int update(final String sql) throws DataAccessException {
		class ExecuteCallback implements DbUtilsCallback<Integer> {
			@Override
			public Integer execute(Connection connection) throws SQLException {
				return getQueryRunner().update(connection, sql);
			}
		}
		return execute(new ExecuteCallback());
	}

	/**
	 * Execute an SQL INSERT, UPDATE, or DELETE query with replacement parameters.
	 *
	 * @param sql The SQL to execute.
	 * @param params The query replacement parameters.
	 * @return The number of rows updated.
	 * @throws DataAccessException if a database access error occurs
	 */
	public int update(final String sql, final Object... params)
			throws DataAccessException {
		class ExecuteCallback implements DbUtilsCallback<Integer> {
			@Override
			public Integer execute(Connection connection) throws SQLException {
				return getQueryRunner().update(connection, sql, params);
			}
		}
		return execute(new ExecuteCallback());
	}

	/**
	 * Execute a JDBC(dbutils) data access operation, implemented as callback
	 * action working on a JDBC Connection. This allows for implementing
	 * arbitrary data access operations, within Spring's managed JDBC
	 * environment: that is, participating in Spring-managed transactions and
	 * converting JDBC SQLExceptions into Spring's DataAccessException
	 * hierarchy.
	 * <p>
	 * The callback action can return a result object, for example a domain
	 * object or a collection of domain objects.
	 * 
	 * @param action
	 * @return
	 * @throws DataAccessException
	 */
	private <T> T execute(DbUtilsCallback<T> action) throws DataAccessException {
		Assert.notNull(action, "Callback object must not be null");
		Connection connection = DataSourceUtils.getConnection(getDataSource());
		try {
			return action.execute(connection);
		} catch (SQLException e) {
			//e.printStackTrace();
			DataSourceUtils.releaseConnection(connection, getDataSource());
			connection=null;
			throw new DbUtilsDataAccessException("execute error:"+e.getMessage(), e.getCause());
		} finally {
			DataSourceUtils.releaseConnection(connection, getDataSource());
		}
	}
}
